require 'mysql2'
require 'time'
require 'awesome_print'

# 创建 MySQL 客户端实例
@client = Mysql2::Client.new({ host: ENV['RDS_AGENT'], username: 'psi_root', port: 1403, password: ENV['PSI_PASSWORD'], database: 'tutem_mall' })

# 查询所有发生过退款的订单
def checkRefundHistory
    query = <<-SQL
        SELECT id, uid, pay_price, refund_price, pay_time, refund_status, remark
        FROM tutem_mall.eb_store_order
        WHERE refund_price > 0 AND pay_type = 'weixin';
    SQL

    results = @client.query(query, symbolize_keys: true)

    # 分析退款情况
    results.each do |row|
        id = row[:id]
        uid = row[:uid]
        pay_price = row[:pay_price].to_f
        refund_price = row[:refund_price].to_f
        balance = (pay_price - refund_price).to_f
        pay_time = Time.at(row[:pay_time]).to_i
        remark = row[:remark] || ''

        refund_time = pay_time + 86400 # 加 24 小时
        admin_id = 0
        balance = pay_price - refund_price
        mark = "A##{admin_id} 系统补录，参考支付时间作为退款时间"

        if remark.include?('微信退款') || remark.include?('refund') || remark.include?('REFUND')
            # 第一种匹配 [yyyy-mm-dd HH:MM:SS A#20] 
            patternA = /\[(\d{4}-\d{2}-\d{2}) (\d{2}:\d{2}:\d{2}) A#(\d+)\]/
            matchA = remark.match(patternA)
            patternB = /\[(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})\] A#(\d+)/
            matchB = remark.match(patternB)
            if matchA || matchB

                if matchA
                    date = matchA[1]
                    time = matchA[2]
                    admin_id = matchA[3].to_i
                end
                if matchB
                    date = matchB[1]
                    time = matchB[2]
                    admin_id = matchB[3].to_i
                end
                refund_time = Time.parse("#{date} #{time}").to_i
                mark = "A##{admin_id} 系统补录，选择备注时间作为退款时间"

            else

                # 第二种匹配 [yyyy-mm-dd HH:MM:SS] #20 正则表达式匹配退款记录
                pattern = /\[([\d\- :]+)\] #([\d]+)/ #这个处理
                matches = remark.scan(pattern)
                if matches.any?
                    # 如果有多个匹配，取最后一个
                    last_match = matches.last
                    refund_time = Time.parse(last_match[0]).to_i
                    admin_id = last_match[1].to_i
                    mark = "A##{admin_id} 系统补录，选择备注时间作为退款时间"
                end

            end
        end

        bill = {
            uid: row[:uid], link_id: row[:id].to_s,
            pm: 1, title: "微信退款", category: "refund_product", type: "weixin_pay",
            number: refund_price, balance: balance, mark: mark, add_time: refund_time,
            status: 1, take: 0, is_hide: 0
        }

        createRefundBill bill
=begin
        # 输出分析结果
        print format("订单号: %5d ", id)
        print format("用户号: %5d ", uid)
        print format("支付额: %8.2f ", pay_price)
        print format("退款额: %8.2f ", refund_price)
        print format("剩余额: %8.2f ", balance)
        print "退款时间: #{refund_time.to_i} (#{refund_time.utc}) "
        print mark
        puts ""
=end

    end
end

def createRefundBill bill

    # 检查是否已存在记录
    check_query = <<~SQL
        SELECT COUNT(*) AS count
        FROM eb_user_bill
        WHERE link_id = ? AND category = ? AND type = ?
    SQL

    exists = @client.prepare(check_query).execute(bill[:link_id], "refund_product", "weixin_pay").first["count"]

    if exists > 0
        puts "Record already exists, skipping insertion."
    else
        # 插入记录
        insert_query = <<~SQL
            INSERT INTO eb_user_bill (uid, link_id, pm, title, category, type, number, balance, mark, add_time, status, take, is_hide)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        SQL

        @client.prepare(insert_query).execute(
            bill[:uid], bill[:link_id], bill[:pm], bill[:title], bill[:category], bill[:type], bill[:number],
            bill[:balance], bill[:mark], bill[:add_time], bill[:status], bill[:take], bill[:is_hide]
        )

        puts "New record inserted successfully."
    end

end

checkRefundHistory
